# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface

from itemadapter import ItemAdapter

import pymysql
from scrapy.utils.project import get_project_settings

class DdReptilePipeline:
    def process_item(self, item, spider):
        return item


class MysqlPipeline:
    def open_spider(self, spider):

        settings = get_project_settings()
        self.host = settings['DB_HOST']
        self.port = settings['DB_PORT']
        self.user = settings['DB_USER']
        self.pass_ = settings['DB_PASS']
        self.name = settings['DB_NAME']
        self.charset = settings['DB_CHARSET']

        self.connection()

        self.fp = open('./insert.sql', mode='a', encoding='utf-8')

    def connection(self):
        self.conn = pymysql.connect(host=self.host,
                                    port=self.port,
                                    user=self.user,
                                    password=self.pass_,
                                    database=self.name,
                                    charset=self.charset)
        self.cursor = self.conn.cursor()


    def process_item(self, item, spider):
        sql = 'insert into dushu_book_info(`book_name`, `author_name`, `category_id`, `category_name`, `comment_number`, `desc`, `media_id`,' \
              ' `original_price`, `paperbook_price`, `price`, `promotion_price`, `sale_price`, `vip_price`)' \
              ' values (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)'
        # 可以将所有的执行sql固化 但是感觉没必要了
        # self.fp.write((sql + ';\n') % ('\''+item['bookName']+'\'', '\''+item["authorName"]+'\'', '\''+item["categoryId"]+'\',' '\''+item['categoryName']+'\'',
        #                                item["commentNumber"], '\''+item["desc"]+'\'', item['mediaId'], item["originalPrice"],
        #                                item["paperBookPrice"], item['price'], item["promotionPrice"], item["salePrice"], item['vipPrice']))
        self.cursor.execute(sql, (item['bookName'], item["authorName"], item["categoryId"], item['categoryName'], item["commentNumber"], item["desc"],item['mediaId'], item["originalPrice"], item["paperBookPrice"], item['price'], item["promotionPrice"], item["salePrice"], item['vipPrice']))
        self.conn.commit()
        return item

    def close_spider(self):
        self.cursor.close()
        self.conn.close()
        self.fp.close()
